package com.qipay.excel.service.impl;

import com.qipay.excel.service.ExcelService;
import com.qipay.excel.vo.Cell;
import com.qipay.excel.vo.Style;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.io.FilenameUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.*;

/**
 * Creator: Yao
 * Date:    2017/12/3
 * For:
 * Other:
 */
@Service
public class ExcelServiceImpl implements ExcelService {

    Logger logger = LoggerFactory.getLogger(ExcelServiceImpl.class);

    // Interface

    /***
     * 文件数据填充
     * @param file
     * @param cells
     * @return
     * @throws IOException
     */
    @Override
    public HSSFWorkbook fill(String file, List<Cell> cells) throws IOException {

        // log
        logger.info("fill({}, List<com.yunkai.component.excel.vo.Cell>)", file);

        // check
        Assert.isTrue(CollectionUtils.isNotEmpty(cells));

        // init
        HSSFWorkbook book = null;
        HSSFSheet sheet = null;
        Row row = null;
        org.apache.poi.ss.usermodel.Cell cell = null;
        File xls = null;
        Map<String, HSSFCellStyle> cellStyleMap = new HashMap<>();

        // book.init
        if (StringUtils.isNoneBlank(file)) {
            xls = new File(file);
        }
        if (null != xls)
            book = new HSSFWorkbook(new FileInputStream(xls));
        else
            book = new HSSFWorkbook();
        sheet = getSheet(book, null);

        // sort
        Collections.sort(cells, new Comparator<Cell>() {
            @Override
            public int compare(Cell o1, Cell o2) {
                if (o1.getRow() > o2.getRow()) {
                    return 1;
                } else if (o1.getRow() < o2.getRow()) {
                    return -1;
                } else {
                    return o1.getColumn() - o2.getColumn();
                }
            }
        });

        // book.write
        int rIdx = 0;
        int cIdx = 0;
        String value = "";
        String key = "";
        HSSFCellStyle cellStyle = null;
        for (Cell p : cells) {

            // init.row
            if (rIdx != p.getRow()) {
                rIdx = p.getRow();
                row = null;
            }
            if (null == row)
                row = rIdx < sheet.getPhysicalNumberOfRows() ? sheet.getRow(rIdx) : sheet.createRow(rIdx);

            // check
            cIdx = p.getColumn();
            value = p.getValue();
            if (StringUtils.isBlank(value)) {
                value = "";
            }

            cell = cIdx < row.getPhysicalNumberOfCells() ? row.getCell(cIdx) : row.createCell(cIdx);
            cell.setCellValue(value);

            key = p.getKey();
            if (cellStyleMap.containsKey(key)) {
                cellStyle = cellStyleMap.get(key);
            } else {
                cellStyle = initStyle(book, p);
                cellStyleMap.put(key, cellStyle);
            }
            cell.setCellStyle(cellStyle);
        }

        return book;
    }

    /***
     * 填充内容
     * @param list
     * @param detailList
     * @param rowIdx
     * @return
     */
    @Override
    public void fill(List<Cell> list, List<List<String>> detailList, int rowIdx) {

        // check
        Assert.isTrue(CollectionUtils.isNotEmpty(list));

        // init
        int cellIdx = 0;

        // fill
        if (CollectionUtils.isNotEmpty(detailList)) {

            // foreach
            for (List<String> p : detailList) {

                // check
                if (CollectionUtils.isEmpty(p))
                    continue;

                // init
                rowIdx++;
                cellIdx = 0;

                // foreach
                for (String q : p) {
                    list.add(new Cell(rowIdx, cellIdx++, q));
                }
            }
        }
    }

    @Override
    public List<List<String>> read(String file) throws IOException {

        logger.info("read({})", file);

        // init
        List<List<String>> list = new ArrayList<>();
        List<String> rowList = null;
        HSSFWorkbook book = null;
        HSSFSheet sheet = null;
        Iterator<Row> rows = null;
        Row row = null;
        Iterator<org.apache.poi.ss.usermodel.Cell> cells = null;
        org.apache.poi.ss.usermodel.Cell cell = null;
        int startIdx = 1;
        int lastCellIdx = -1;

        // check
        Assert.isTrue(StringUtils.isNotBlank(file), "excel is not supported");
        File xls = new File(file);
        Assert.isTrue(FilenameUtils.getExtension(file).toLowerCase().startsWith("xls"), String.format("%s is not excel", file));
        Assert.isTrue(xls.exists(), String.format("%s is not exists", file));

        // book.init
        book = new HSSFWorkbook(new FileInputStream(xls));
        sheet = getSheet(book, null);

        // book.read
        int size = sheet.getLastRowNum();
        DataFormatter formatter = new DataFormatter();
        for (int i = startIdx - 1; i <= size; i++) {

            row = sheet.getRow(i);
            lastCellIdx = -1;
            if (null == row)
                continue;

            rowList = new ArrayList<>();
            for (cells = row.cellIterator(); cells.hasNext(); ) {

                // init
                cell = cells.next();

                // fill.empty.column
                int cellIdx = cell.getColumnIndex();
                fillEmptyCell(rowList, (cellIdx != lastCellIdx + 1) ? (cellIdx - lastCellIdx - 1) : 0);
                lastCellIdx = cellIdx;

                // read
                rowList.add(formatter.formatCellValue(cell));
            }
            list.add(rowList);
        }

        // close
//         QiPay 中无法运行
        try {
//            IOUtils.closeQuietly(book);
            book.close();
        } catch (Exception e) {
            // ignore
        }

        return list;
    }


    // Commons

    /**
     * 获取指定名称的页面
     *
     * @param book
     * @param sheetName 获取指定名称页面，若未找到或未指定，则默认获取第一页
     * @return
     */
    private HSSFSheet getSheet(HSSFWorkbook book, String sheetName) {

        HSSFSheet sheet = null;

        if (StringUtils.isNotBlank(sheetName))
            sheet = book.getSheet(sheetName);
        if (null == sheet)
            sheet = book.getNumberOfSheets() > 0 ? book.getSheetAt(0) : book.createSheet();

        return sheet;
    }

    /***
     * 样式填充
     * @param book
     * @param p
     * @return
     */
    private HSSFCellStyle initStyle(HSSFWorkbook book, Style p) {

        // init
        HSSFCellStyle style = book.createCellStyle();

        // fill
        style.setAlignment(p.getAlign());
        style.setVerticalAlignment(p.getvAlign());
        HSSFFont font = book.createFont();
        font.setFontName(p.getFont());
        font.setFontHeightInPoints(p.getFontSize());
        font.setColor(p.getFontColor());
        style.setFont(font);
        // 单元格背景色
        style.setFillForegroundColor(p.getBackgroundColor());
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        // 边框
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 允许换行 /r/n
//        style.setWrapText(true);

        return style;
    }

    private void fillEmptyCell(List<String> rowList, int length) {
        for (int j = 0; j < length; j++)
            rowList.add("");
    }

}
